package cn.ainit.edi.app;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.SAXReader;
import org.dom4j.io.XMLWriter;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import cn.ainit.byf.station.Station;
import cn.ainit.common.DzEDIType;
import cn.ainit.common.ReturnObject;
import cn.ainit.edi.entity.FormatTablesFromFile;
import cn.ainit.edi.entity.SysDBSchemaCodeList;
import cn.ainit.edi.entity.SysDBSchemaInfo;
import cn.ainit.exchange.entity.ExchangeColumn;
import cn.ainit.exchange.entity.ExchangeGroup;
import cn.ainit.exchange.entity.ExchangeTable;
import cn.ainit.exchange.entity.MappingGroup;
import cn.ainit.exchange.entity.MappingTable;
import cn.ainit.sys.constant.DzConstant;
import cn.ainit.sys.constant.SystemConstant;
import cn.ainit.sys.menu.SessionCont;
import cn.ainit.sys.query.datastore.DataStore;
import cn.ainit.sys.query.entity.DataGridReceiveParams;
import cn.ainit.sys.query.entity.QueryJson;
import cn.ainit.sys.utils.FTPUtils;
import cn.ainit.sys.utils.UUID32;

/***
 * 依赖包：jaxen.jar dom4j.jar 主要实现： 数据库数据导出 1.定义导出规则名，导出类型（xml txt定长等。。。） 2.列设置
 * 
 * 
 * 数据通过文件导入到系统 1.根据xml，创建表结构 2.根据文件类型，导入数据到对应的表。 3.导入类型 xml txt定长
 * 
 * 
 * @author xuzq
 */
@Controller
@RequestMapping("/edi")
public class SysEDIController {
	SysEDIService sysEDIService;

	@Resource
	public void setSysEDIService(SysEDIService sysEDIService) {
		this.sysEDIService = sysEDIService;
	}

	/**************************************************************
	 * 上传XML文件至FTP指定文件夹
	 *************************************************************/
	@RequestMapping("/uploadXML")
	@ResponseBody
	public Map<String, String> uploadXML(HttpServletRequest request)
			throws Exception {
		  // 构造Properties对象  
        Properties properties = new Properties(); 
        InputStream is_ = SysEDIController.class.getResourceAsStream("/config/sys/ftp_upload.properties"); 
        properties.load(is_); 
	    String  port = (String) properties.get("port"); // 设置端口  
	    String username = (String) properties.get("username"); // 设置用户名  
	    String password = (String) properties.get("password"); // 设置密码
	    String ip = (String) properties.get("ip");
      
		Date timestamp_1 = new Date();
		FTPUtils ftpUtils = FTPUtils.getInstance(port,username,password);
		FileInputStream is = null;
		Map<String, String> map = new HashMap<String, String>();
		// 获取参数
		// 服务器本地存储路径
		String LOCALPATH = request.getParameter("LOCALPATH");
		// FTP存储路径
		String FTPPATH = request.getParameter("FTPPATH");
		// 自定义范围
		String CUSTOMRANGE = request.getParameter("CUSTOMRANGE");
		// 类型
		String TYPE = request.getParameter("TYPE");
		//传送标题sql
		String	SQLTITLE = request.getParameter("SQLTITLE");
		//总目sql
		String	SQL = request.getParameter("SQL");
	    //细目sql
		String SQLITME = request.getParameter("SQLITME");
		
		// 获取文件名称     String	
		// int one = LOCALPATH.lastIndexOf("/");
		// String filename = LOCALPATH.substring((one+1),LOCALPATH.length());
		// 收集数据===>传递参数:类型,自定义范围(where后面的语句)
		Map<String, List<Map<String, Object>>> dataMap = sysEDIService.xmlIm(
				TYPE, CUSTOMRANGE,SQLTITLE,SQL,SQLITME);
		if (dataMap.get("kcsp").size()==0 || dataMap.get("title").size()==0) {
			map.put("msg", "未收集到生成XML的数据");
			return map;
		}
		String filename = dataMap.get("title").get(0).get("传送文件ID") + ".xml";
		File localFile = new File(LOCALPATH + filename);
		// 一次生成多个XML文件
		if (SystemConstant.EDI_Amazon_cgqud_upload.equals(TYPE)
				|| SystemConstant.EDI_Amazon_fhd_upload.equals(TYPE)) {
			writeBook(TYPE, LOCALPATH, dataMap, filename, FTPPATH,ip);
			return null;
		}
		// 一次生成单个XML文件
		// 服务器本地文件若存在则不再生成
		else if (!localFile.exists()) {
			// 生成XML文件==>参数:类型,服务器输出路径,数据源,XML文件名称
			writeBook(TYPE, LOCALPATH + filename, dataMap, filename, null,ip);
		}
		try {
			// 读取要上传的XML文件
			is = new FileInputStream(localFile);

			// 上传到FTP,参数===>服务器地址,存储路径,存储名称,流
			boolean b = ftpUtils.storeFile(ip, FTPPATH,
					filename, is);
			Date timestamp_2 = new Date();
			SimpleDateFormat simpleDateTimeFormat = new SimpleDateFormat(
					" yyyy-MM-dd HH:mm:ss ");
			// 日志保存
			sysEDIService.saveLog("亚马逊", "上传", b, "", filename,
					simpleDateTimeFormat.format(timestamp_1.getTime()),
					simpleDateTimeFormat.format(timestamp_2.getTime()),
					localFile.length(), FTPPATH, LOCALPATH + filename, "", "");
			System.out.println("是否上传成功" + b);
			if (!b) {
				map.put("msg", "error");
			} else {
				map.put("msg", "success");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			is.close();
		}

		return map;

	}

	/**************************************************************
	 * 下载FTP指定文件夹里的XML文件
	 *************************************************************/
	@RequestMapping("/downloadXML")
	@ResponseBody
	public Map<String, String> downloadXML(String id, HttpServletRequest request)
			throws Exception {
		 Properties properties = new Properties(); 
	        InputStream is_ = SysEDIController.class.getResourceAsStream("/config/sys/ftp_download.properties"); 
	        properties.load(is_); 
		    String  port = (String) properties.get("port"); // 设置端口  
		    String username = (String) properties.get("username"); // 设置用户名  
		    String password = (String) properties.get("password"); // 设置密码
		    String ip = (String) properties.get("ip");
		    
		// 获取参数
		// 服务器本地存储路径
		String LOCALPATH = request.getParameter("LOCALPATH");
		// FTP存储路径
		String FTPPATH = request.getParameter("FTPPATH");
		// 自定义范围
		String CUSTOMRANGE = request.getParameter("CUSTOMRANGE");
		// 类型
		String TYPE = request.getParameter("TYPE");
		//过程名称
		String STOREDPROCEDURE = request.getParameter("STOREDPROCEDURE");
		
		FTPUtils ftpUtils = FTPUtils.getInstance(port,username,password);
		
//		InputStream in_por = SysEDIController.class
//				.getResourceAsStream("/config/sys/ftp.properties");
//		pro.load(in_por);
		FileOutputStream out = null;
		InputStream is = null;
		Map<String, String> map = new HashMap<String, String>();
		//ftp-cn.amazon.com   pro.getProperty("ip")
		// 连接FTP
		boolean b1 = ftpUtils.connectToTheServer(ip, FTPPATH);
		System.out.println("是否连接上FTP:" + b1);
		// 读取下载文件名称列表 "/" +
		List<String> list = ftpUtils.getFileNameList(FTPPATH);

		if (!b1) {
			map.put("msg", "error");
		} else {
			map.put("msg", "success");
		}
		for (String s : list) {
			Date timestamp_1 = new Date();
			File file = new File(LOCALPATH + "/" + s);
			try {
				// 从FTP下载到服务器本地
				is = ftpUtils.retrieveFile(ip, FTPPATH, s);
				if(is==null)
					continue;
				out = new FileOutputStream(file);
				int n = 0;// 每次读取的字节长度
				byte[] bb = new byte[1024];// 存储每次读取的内容
				while ((n = is.read(bb)) != -1) {
					
					out.write(bb, 0, n);// 将读取的内容，写入到输出流当中
				}

			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				// finally 关闭
			//	in_por.close();
				if(is != null)
					is.close();
				if(out != null)
					out.close();// 关闭输入输出流
			}
			// 文件下载完成后,调用解析方法
			Map<String, Object> sqlMap = xmlEx(LOCALPATH, s);
			//存入过程名
			sqlMap.put("STOREDPROCEDURE", STOREDPROCEDURE);
			// 存入相应的临时表中
			sysEDIService.saveXml(sqlMap);
			Date timestamp_2 = new Date();
			//保存日志
			SimpleDateFormat simpleDateTimeFormat = new SimpleDateFormat(" yyyy-MM-dd HH:mm:ss ");
			sysEDIService.saveLog("亚马逊", "下载", true, "", s,
					simpleDateTimeFormat.format(timestamp_1.getTime()),
					simpleDateTimeFormat.format(timestamp_2.getTime()),
					file.length(), FTPPATH, LOCALPATH + "/" + s, "", "");
			//最后删除FTP里的文件
			ftpUtils.deleteFile(ip, FTPPATH, s);
			
			
		}

		return map;
	}
			///下载
	private Map<String, Object> xmlEx(String path, String file) {
		// 截取文件名,获得单据类型
		String[] str = file.split("_");
		String fileType = str[0];
		String sql_title = "";
		String sql = "";
		List<String> sql_itme = new ArrayList<String>();
		//
		String columns_title = "";
		String values_title = "";
		//
		String columns_ = "";
		String values_ = "";
		
		// List<String> columns_itme =new ArrayList<String>();
		// List<String> values_itme =new ArrayList<String>();
		// 读取服务器本地XML文件
		File inputXml = new File(path + "/" + file);
		String UUID = UUID32.newGuid();
		SAXReader saxReader = new SAXReader();

		// 采购单
		if ("采购".equals(fileType)) {
			try {
				Document document = saxReader.read(inputXml);
				Element users = document.getRootElement();
				// 图书流通信息
				System.out.println(users.getName());
				//
				int k = 0;
				for (Iterator i = users.elementIterator(); i.hasNext();) {
					//
					String columns_itme_str = "";
					String values_itme_str = "";

					Element user = (Element) i.next();
					//
					for (Iterator j = user.elementIterator(); j.hasNext();) {
						Element node = (Element) j.next();
						if ("传送标题".equals(user.getName())) {
							columns_title += "SY_" + node.getName() + ",";
							values_title += "'" + node.getText() + "',";
						} else if ("采购单据总目".equals(user.getName())) {
							columns_ += "SY_" + node.getName() + ",";
							values_ += "'" + node.getText() + "',";
						} else if ("采购单据细目".equals(user.getName())) {
							columns_itme_str += "SY_" + node.getName() + ",";
							values_itme_str += "'" + node.getText() + "',";
						}
					}
					if ("采购单据细目".equals(user.getName()))
						sql_itme.add("insert into \"ZD_采购单据细目\"(" + columns_itme_str
								+ "XML_GLID)values(" + values_itme_str + "'"
								+ UUID + "')");

				}
			} catch (DocumentException e) {
				System.out.println(e.getMessage());
			}
			sql_title = "insert into \"ZD_传送标题\"(" + columns_title
					+ "XML_GLID)values(" + values_title + "'" + UUID + "')";
			sql = "insert into \"ZD_采购单据总目\"(" + columns_ + "XML_GLID)values("
					+ values_ + "'" + UUID + "')";

			// 退货单
		} else if ("退货".equals(fileType)) {
			try {
				Document document = saxReader.read(inputXml);
				Element users = document.getRootElement();
				// 图书流通信息
				System.out.println(users.getName());
				//
				int k = 0;
				for (Iterator i = users.elementIterator(); i.hasNext();) {
					//
					String columns_itme_str = "";
					String values_itme_str = "";
					Element user = (Element) i.next();
					//
					for (Iterator j = user.elementIterator(); j.hasNext();) {
						Element node = (Element) j.next();
						if ("传送标题".equals(user.getName())) {
							columns_title += "SY_" + node.getName() + ",";
							values_title += "'" + node.getText() + "',";
						} else if ("退货单据总目".equals(user.getName())) {
							columns_ += "SY_" + node.getName() + ",";
							values_ += "'" + node.getText() + "',";
						} else if ("退货单据细目".equals(user.getName())) {
							columns_itme_str += "SY_" + node.getName() + ",";
							values_itme_str += "'" + node.getText() + "',";
						}
					}
					if ("退货单据细目".equals(user.getName()))
					{
						sql_itme.add("insert into \"XT_退货单据细目\"(" + columns_itme_str
								+ "XML_GLID)values(" + values_itme_str + "'"
								+ UUID + "')");
						columns_itme_str = "";
						values_itme_str = "";
					}
						

				}
			} catch (DocumentException e) {
				System.out.println(e.getMessage());
			}
			sql_title = "insert into \"XT_传送标题\"(" + columns_title
					+ "XML_GLID)values(" + values_title + "'" + UUID + "')";
			sql = "insert into \"XT_退货单据总目\"(" + columns_ + "XML_GLID)values("
					+ values_ + "'" + UUID + "')";

		}
		// 销售报告
		else if ("POS".equals(fileType)) {
			sql = "";
		}
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("sql_title", sql_title);
		map.put("sql", sql);
		map.put("sql_itme", sql_itme);
		map.put("GLID", UUID);
		map.put("fileType", fileType);
		return map;
	}

	/**
	 * 生成到指定名字的xml文件中(上传)
	 * 
	 * @param filepathname
	 *            要生成的xml文件名
	 */
	public void writeBook(String TYPE, String filepathname,
			Map<String, List<Map<String, Object>>> dataMap, String name,
			String FTPPATH,String ip) {
		// 商品库存信息上传//////////////////////////////////////////////////////////////////////////////
		if (SystemConstant.EDI_Amazon_spkcxx_upload.equals(TYPE)) {
			// 1. 构造空的Document
			Document doc = DocumentHelper.createDocument();
			// 2. 构造根元素
			Element rootElmt = doc.addElement("图书流通信息");
			// 3. 递归构造子元素
			// <传送标题>
			Element titleElmt = rootElmt.addElement("传送标题");
			for (Map<String, Object> map : dataMap.get("title")) {
				Set<Map.Entry<String, Object>> set = map.entrySet();
				for (Iterator<Map.Entry<String, Object>> it = set.iterator(); it
						.hasNext();) {
					Map.Entry<String, Object> entry = (Map.Entry<String, Object>) it
							.next();
					if(!"GLID".equals(entry.getKey())){
						// 设置key
						Element zongmu_bs = titleElmt.addElement(entry.getKey());
						// 设置value
						if (entry.getValue() == null || "".equals(entry.getValue())) {
						} else {
							zongmu_bs.setText(entry.getValue() + "");
						}
					}
					
				}
			}
			// <库存商品>
			for (Map<String, Object> map : dataMap.get("kcsp")) {
				Element zongmuElmt = rootElmt.addElement("库存商品");
				Set<Map.Entry<String, Object>> set = map.entrySet();
				for (Iterator<Map.Entry<String, Object>> it = set.iterator(); it
						.hasNext();) {
					Map.Entry<String, Object> entry = (Map.Entry<String, Object>) it
							.next();
					// 设置key
					Element zongmu_bs = zongmuElmt.addElement(entry.getKey());
					// 设置value
					if (entry.getValue() == null || "".equals(entry.getValue())) {
					} else {
						zongmu_bs.setText(entry.getValue() + "");
					}
				}
			}
			// <采购单据细目>
			// Element ximuElmt = rootElmt.addElement("采购单据细目");
			// Element ximu_bs = ximuElmt.addElement("数据标识");
			// ximu_bs.setText("Order");
			// 4. 输出
			outputXml(doc, filepathname);
		}
		// ////////////////////////////亚马逊基础信息/////////////////////////////////////////
		else if (SystemConstant.EDI_Amazon_spjcxx_upload.equals(TYPE)) {
			// 1. 构造空的Document
			Document doc = DocumentHelper.createDocument();
			// 2. 构造根元素
			Element rootElmt = doc.addElement("图书流通信息");
			Element titleElmt = rootElmt.addElement("传送标题");
			for (Map<String, Object> map : dataMap.get("title")) {
				Set<Map.Entry<String, Object>> set = map.entrySet();
				for (Iterator<Map.Entry<String, Object>> it = set.iterator(); it
						.hasNext();) {
					Map.Entry<String, Object> entry = (Map.Entry<String, Object>) it
							.next();
					if(!"GLID".equals(entry.getKey())){
					// 设置key
					Element zongmu_bs = titleElmt.addElement(entry.getKey());
					// 设置value
					if (entry.getValue() == null || "".equals(entry.getValue())) {
					} else {
						zongmu_bs.setText(entry.getValue() + "");
					}
					}
				}
			}
			// <图书商品>
			for (Map<String, Object> map : dataMap.get("kcsp")) {
				Element zongmuElmt = rootElmt.addElement("库存商品");
				Set<Map.Entry<String, Object>> set = map.entrySet();
				for (Iterator<Map.Entry<String, Object>> it = set.iterator(); it
						.hasNext();) {
					Map.Entry<String, Object> entry = (Map.Entry<String, Object>) it
							.next();
					// 设置key
					Element zongmu_bs = zongmuElmt.addElement(entry.getKey());
					// 设置value
					if (entry.getValue() == null || "".equals(entry.getValue())) {
					} else {
						zongmu_bs.setText(entry.getValue() + "");
					}
				}
			}
			// 4. 输出
			outputXml(doc, filepathname);

		}
		// /////////////////////////////////亚马逊采购确认单////////////////////////////////////////////////////////
		else if (SystemConstant.EDI_Amazon_cgqud_upload.equals(TYPE)) {
			Date timestamp_1 = new Date();
			for (Map<String, Object> map_title : dataMap.get("title")) {
				Document doc = DocumentHelper.createDocument();
				Element rootElmt = doc.addElement("图书流通信息");
				Element titleElmt = rootElmt.addElement("传送标题");
				Set<Map.Entry<String, Object>> set_title = map_title.entrySet();
				for (Iterator<Map.Entry<String, Object>> it = set_title
						.iterator(); it.hasNext();) {
					Map.Entry<String, Object> entry = (Map.Entry<String, Object>) it
							.next();
					if(!"GLID".equals(entry.getKey())){
					// 设置key
					Element zongmu_bs = titleElmt.addElement(entry.getKey());
					// 设置value
					if (entry.getValue() == null || "".equals(entry.getValue())) {
					} else {
						zongmu_bs.setText(entry.getValue() + "");
					}
					}
				}
			//  对比标识(单据号码)
			//	String num = (String) map_title.get("传送文件ID");
			//	String StrNum = num.substring(num.lastIndexOf("_") + 1);
				// <总目>
				for (Map<String, Object> map : dataMap.get("kcsp")) {
					if (map.get("GLID").equals(map_title.get("GLID"))) {
						Element zongmuElmt = rootElmt.addElement("采购确认单据总目");
						Set<Map.Entry<String, Object>> set = map.entrySet();
						for (Iterator<Map.Entry<String, Object>> it = set
								.iterator(); it.hasNext();) {
							Map.Entry<String, Object> entry = (Map.Entry<String, Object>) it
									.next();
							// 设置key
							if (!"GLID".equals(entry.getKey())) {
								Element zongmu_bs = zongmuElmt.addElement(entry
										.getKey());
								// 设置value
								if (entry.getValue() == null
										|| "".equals(entry.getValue())) {
								} else {
									zongmu_bs.setText(entry.getValue() + "");
								}
							}
						}
					}
					// <细目>
					for (Map<String, Object> map_time : dataMap.get("kcsp_itme")) {
						if (map.get("GLID").equals(map_time.get("GLID")) && map.get("GLID").equals(map_title.get("GLID"))) {
							Element zongmuElmt_itme = rootElmt
									.addElement("采购确认单据细目");
							Set<Map.Entry<String, Object>> set_itme = map_time
									.entrySet();
							for (Iterator<Map.Entry<String, Object>> it = set_itme.iterator(); it.hasNext();) {
								Map.Entry<String, Object> entry = (Map.Entry<String, Object>) it.next();
								// 设置key
								if (!"GLID".equals(entry.getKey())) {
									Element zongmu_bs = zongmuElmt_itme
											.addElement(entry.getKey());
									// 设置value
									if (entry.getValue() == null
											|| "".equals(entry.getValue())) {
									} else {
										zongmu_bs.setText(entry.getValue()
												+ "");
									}
								}
							}
						}
					}
				

				}
				// 4. 输出
				outputXml(doc, filepathname + map_title.get("传送文件ID") + ".xml");
				// 上传FTP
				FileInputStream is = null;
				File localFile = new File(filepathname
						+ map_title.get("传送文件ID") + ".xml");
				try {
					// 读取要上传的XML文件
					is = new FileInputStream(localFile);

					// 上传到FTP,参数===>服务器地址,存储路径,存储名称,流
					boolean b = FTPUtils.storeFile(ip,
							FTPPATH, map_title.get("传送文件ID") + ".xml", is);
					Date timestamp_2 = new Date();
					SimpleDateFormat simpleDateTimeFormat = new SimpleDateFormat(
							" yyyy-MM-dd HH:mm:ss ");
					// 日志保存
					sysEDIService.saveLog("亚马逊", "上传", b, "", map_title
							.get("传送文件ID")
							+ ".xml", simpleDateTimeFormat.format(timestamp_1
							.getTime()), simpleDateTimeFormat
							.format(timestamp_2.getTime()), localFile.length(),
							FTPPATH, filepathname + map_title.get("传送文件ID")
									+ ".xml", "", "");
					System.out.println("是否上传成功" + b);
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} finally {
					try {
						is.close();
					} catch (IOException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}
		} else if (SystemConstant.EDI_Amazon_fhd_upload.equals(TYPE)) {
			Date timestamp_1 = new Date();
			for (Map<String, Object> map_title : dataMap.get("title")) {
				Document doc = DocumentHelper.createDocument();
				Element rootElmt = doc.addElement("图书流通信息");
				Element titleElmt = rootElmt.addElement("传送标题");
				Set<Map.Entry<String, Object>> set_title = map_title.entrySet();
				for (Iterator<Map.Entry<String, Object>> it = set_title
						.iterator(); it.hasNext();) {
					Map.Entry<String, Object> entry = (Map.Entry<String, Object>) it
							.next();
					if(!"GLID".equals(entry.getKey())){
					// 设置key
					Element zongmu_bs = titleElmt.addElement(entry.getKey());
					// 设置value
					if (entry.getValue() == null || "".equals(entry.getValue())) {
					} else {
						zongmu_bs.setText(entry.getValue() + "");
					}
					}
				}
				//String num = (String) map_title.get("传送文件ID");
				//String StrNum = num.substring(num.lastIndexOf("_") + 1);
				// <总目>
				for (Map<String, Object> map : dataMap.get("kcsp")) {
					if (map.get("GLID").equals(map_title.get("GLID"))) {
						Element zongmuElmt = rootElmt.addElement("发货单据总目");
						Set<Map.Entry<String, Object>> set = map.entrySet();
						for (Iterator<Map.Entry<String, Object>> it = set
								.iterator(); it.hasNext();) {
							Map.Entry<String, Object> entry = (Map.Entry<String, Object>) it
									.next();
							// 设置key
							if (!"GLID".equals(entry.getKey())) {
								Element zongmu_bs = zongmuElmt.addElement(entry
										.getKey());
								// 设置value
								if (entry.getValue() == null
										|| "".equals(entry.getValue())) {
								} else {
									zongmu_bs.setText(entry.getValue() + "");
								}
							}

						}
						// <细目>
						for (Map<String, Object> map_time : dataMap
								.get("kcsp_itme")) {
							if (map.get("GLID").equals(map_time.get("GLID")) && map.get("GLID").equals(map_title.get("GLID"))) {
								Element zongmuElmt_itme = rootElmt
										.addElement("发货单据细目");
								Set<Map.Entry<String, Object>> set_itme = map_time
										.entrySet();
								for (Iterator<Map.Entry<String, Object>> it = set_itme
										.iterator(); it.hasNext();) {
									Map.Entry<String, Object> entry = (Map.Entry<String, Object>) it
											.next();
									// 设置key
									if (!"GLID".equals(entry.getKey())) {
										Element zongmu_bs = zongmuElmt_itme
												.addElement(entry.getKey());
										// 设置value
										if (entry.getValue() == null
												|| "".equals(entry.getValue())) {
										} else {
											zongmu_bs.setText(entry.getValue()
													+ "");
										}
									}
								}
							}
						}
					}

				}
				// 4. 输出
				outputXml(doc, filepathname + map_title.get("传送文件ID") + ".xml");
				// 上传FTP
				 
				FileInputStream is = null;
				File localFile = new File(filepathname
						+ map_title.get("传送文件ID") + ".xml");
				try {
					// 读取要上传的XML文件
					is = new FileInputStream(localFile);
					// 上传到FTP,参数===>服务器地址,存储路径,存储名称,流
					boolean b = FTPUtils.storeFile(ip,
							FTPPATH, map_title.get("传送文件ID") + ".xml", is);
					Date timestamp_2 = new Date();
					SimpleDateFormat simpleDateTimeFormat = new SimpleDateFormat(
							" yyyy-MM-dd HH:mm:ss ");
					// 日志保存
					sysEDIService.saveLog("亚马逊", "上传", b, "", map_title
							.get("传送文件ID")
							+ ".xml", simpleDateTimeFormat.format(timestamp_1
							.getTime()), simpleDateTimeFormat
							.format(timestamp_2.getTime()), localFile.length(),
							FTPPATH, filepathname + map_title.get("传送文件ID")
									+ ".xml", "", "");
					System.out.println("是否上传成功" + b);
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} finally {
					try {
						is.close();
					} catch (IOException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}
		}

	}

	/**
	 * 将doc写出到filename中
	 * 
	 * @param doc
	 *            文档对象
	 * @param filename
	 *            要输出的xml文件名
	 */
	public static void outputXml(Document doc, String filename) {
		try {
			// 定义输出流的目的地
			// FileWriter fw = new FileWriter(filename);

			// 定义输出格式和字符集
			OutputFormat format = OutputFormat.createPrettyPrint();
			format.setEncoding("UTF-8");

			// 定义用于输出xml文件的XMLWriter对象
			FileOutputStream fStream = new FileOutputStream(filename);
			XMLWriter xmlWriter = new XMLWriter(fStream, format);
			xmlWriter.write(doc);// *****
			xmlWriter.close();

		} catch (IOException e) {
			e.printStackTrace();
		}

	}

	// ////////////////////////////////////////////////////////////////////////////////////////////
	// 访问设置界面
	@RequestMapping("/setSchemaInfo")
	public String setSchema(String id, HttpServletRequest request) {
		request.setAttribute("pk", id);
		return "/homepages/edi/importSetting";
	}

	// 导入文件 创建表
	@RequestMapping("/saveSchemaInfo")
	@ResponseBody
	public String saveSchema(
			@RequestParam(value = "file", required = false) MultipartFile file,
			SysDBSchemaInfo schemaInfo, HttpServletRequest request) {
		ReturnObject returnObject = new ReturnObject();
		String path = request.getSession().getServletContext().getRealPath(
				"upload");
		String fileName = file.getOriginalFilename();
		try {
			File targetFile = new File(path, fileName);
			if (!targetFile.exists()) {
				targetFile.mkdirs();
			}
			// 保存到服务器
			file.transferTo(targetFile);

			// 解析文件
			FormatTablesFromFile ftff = new FormatTablesFromFile(schemaInfo
					.getParseType(), targetFile, schemaInfo.getTablePrefix());
			List<StringBuffer> sqlBuffers = ftff.getTableSqls();
			if (sqlBuffers.size() >= 0) {
				schemaInfo.setTableCount(Double.parseDouble(sqlBuffers.size()
						+ ""));
				for (int i = 0; i < sqlBuffers.size(); i++) {
					// 创建表
					System.out.println(sqlBuffers.get(i));
					sysEDIService.executeSql(sqlBuffers.get(i).toString());
				}
				// 保存字段列表信息
				List<SysDBSchemaCodeList> schemaColumns = new ArrayList<SysDBSchemaCodeList>();
				Map<String, List<String>> tableColumnsMap = ftff
						.getTableColumns();
				for (Iterator i = tableColumnsMap.entrySet().iterator(); i
						.hasNext();) {
					Map.Entry entry = (Map.Entry) i.next();
					String tableName = (String) entry.getKey();
					List<String> columns = (List<String>) entry.getValue();
					for (int j = 0; j < columns.size(); j++) {
						SysDBSchemaCodeList codeList = new SysDBSchemaCodeList();
						codeList.setSchemaTypeId(schemaInfo.getSchemaTypeId());
						codeList.setTableName(tableName);
						codeList.setColumnName(columns.get(j));
						schemaColumns.add(codeList);
					}
				}
				sysEDIService.saveSchemaColumns(schemaColumns);
			} else {
				schemaInfo.setTableCount(0.0);
			}
			schemaInfo.setSchemaData("NO Data");
			schemaInfo.setDataDBI(SystemConstant.EDI_TABLE_PREFIX);
			schemaInfo.setOidInput((String) request.getSession().getAttribute(
					SessionCont.uid));
			schemaInfo.setInputDate(new Timestamp(System.currentTimeMillis()));
			schemaInfo.setCreated(schemaInfo.getCreated() == null ? "F" : "T");
			schemaInfo
					.setSuccessMoveFile(schemaInfo.getSuccessMoveFile() == null ? "F"
							: "T");
			// 2.保存文件信息
			boolean flagE = sysEDIService.saveSchema(schemaInfo);
			if (flagE) {
				// 保存文件内容
				sysEDIService.saveSchemaFile(file.getInputStream(), schemaInfo
						.getSchemaTypeId());
			}
			System.out.println(targetFile.getPath());
			// if(targetFile.exists()){
			// targetFile.delete();
			// }
			// 删除文件
			File delFile = new File(targetFile.getPath());
			if (!delFile.isDirectory()) {
				delFile.delete();
			}
			returnObject.setInfo(sqlBuffers.size() + "");
			returnObject.setMsg("Success");
		} catch (Exception e) {
			e.printStackTrace();
			returnObject.setMsg("InvalidFile");
		}
		return "<script>parent.callBack('" + returnObject.getMsg() + "','"
				+ returnObject.getInfo() + "');</script>";
	}

	/***
	 * 更新文件信息
	 * 
	 * @param file
	 * @param schemaInfo
	 * @param request
	 * @return
	 */
	@RequestMapping("/updateSchemaInfo")
	@ResponseBody
	public String update(
			@RequestParam(value = "file", required = false) MultipartFile file,
			SysDBSchemaInfo schemaInfo, HttpServletRequest request) {

		// 1.判断数据库中是否存在该ID
		// 2.有则获取文件、拼接删除表语句、删除缓存表
		// 3.读取文件保存到数据库、重新创建缓存表
		return "";// "<script>parent.callBack('"+returnObject.getMsg()+"','"+returnObject.getInfo()+"');</script>";
	}

	/****************************************************************
	 *数据导出方法 ExchangeGroup
	 ********************************************************/
	/***
	 * 跳转到查询数据源界面
	 */
	@RequestMapping("/queryExcGroup")
	@ResponseBody
	public QueryJson queryExcGroup(DataGridReceiveParams dataGParams,
			String config, HttpServletRequest request) {
		QueryJson queryJson = new QueryJson();
		try {
			QueryJson tmpQJ = sysEDIService.findExcGroup(dataGParams.getRows(),
					dataGParams.getPage(), config);
			queryJson.setRows(tmpQJ.getRows());
			queryJson.setTotal(tmpQJ.getTotal());
			queryJson.setUrl("../edi/queryExcGroup");
			// request.setAttribute("queryJson", queryJson);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return queryJson;
	}

	/**
	 * 添加数据分组
	 */
	@RequestMapping("/addExcGroup")
	@ResponseBody
	public ReturnObject addGroup(ExchangeGroup group, HttpServletRequest request) {
		ReturnObject returnObject = new ReturnObject();
		try {
			if (group.getGroupId() != null && !group.getGroupId().equals("")) {
				sysEDIService.updateExcGroup(group);
				returnObject.setInfo("UpdateSuccess");
			} else {
				String pid = UUID32.newGuid();
				group.setGroupId(pid);
				sysEDIService.saveExcGroup(group);
				returnObject.setMsg(pid);
				returnObject.setInfo("SaveSuccess");
			}
		} catch (Exception e) {
			e.printStackTrace();
			returnObject.setInfo(e.getMessage());
		}
		return returnObject;
	}

	/***
	 * 加载站点和数据源类型
	 */
	@RequestMapping("/loadSourceInfo")
	@ResponseBody
	public List<Object> loadSourceInfo(String config, HttpServletRequest request) {
		List<Object> list = new ArrayList<Object>();
		try {
			List<Station> stations = sysEDIService.queryStation();
			list.add(stations);
			List<DzEDIType> ediTypes = sysEDIService.querySourceType();
			list.add(ediTypes);// 数据源类型
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/****************************************************************
	 *数据导出方法 ExchangeTable
	 ********************************************************/
	/***
	 * 加载exchangeGroup
	 */
	@RequestMapping("/loadExchangeG")
	@ResponseBody
	public List<ExchangeGroup> queryExchangeG(String GroupId,
			HttpServletRequest request) {
		List<ExchangeGroup> exchangeGroups = new ArrayList<ExchangeGroup>();
		try {
			if (GroupId == null || GroupId.equals("")) {
				exchangeGroups = sysEDIService.queryAllExchangeG();
			} else {
				exchangeGroups = sysEDIService.queryExchangeGById(GroupId);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return exchangeGroups;
	}

	/***
	 * 加载exchangeTable
	 */
	@RequestMapping("/loadExchangeTable")
	@ResponseBody
	public List<Object> queryExcTabByGroupId(String groupId,
			HttpServletRequest request) {
		List<ExchangeTable> exchangeTables = new ArrayList<ExchangeTable>();
		List<ExchangeTable> rootExchangeTables = new ArrayList<ExchangeTable>();
		List<Object> trees = new ArrayList<Object>();
		try {
			rootExchangeTables = sysEDIService.queryExcTabByGroupId(groupId,
					"R");
			exchangeTables = sysEDIService.queryExcTabByGroupId(groupId, "");
			trees.add(rootExchangeTables);
			trees.add(exchangeTables);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return trees;
	}

	@RequestMapping("/loadDataSourceType")
	@ResponseBody
	public List<DzEDIType> loadSourceInfo(HttpServletRequest request) {
		List<DzEDIType> ediTypes = new ArrayList<DzEDIType>();
		try {
			ediTypes = sysEDIService.querySourceType();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ediTypes;
	}

	/***
	 * 保存/更新交换项
	 * 
	 * @param exchangeTable
	 * @param request
	 * @return
	 */
	@RequestMapping("/saveExchangeTable")
	@ResponseBody
	public ReturnObject saveExchangeTable(ExchangeTable exchangeTable,
			HttpServletRequest request) {
		ReturnObject returnObject = new ReturnObject();
		try {
			if (exchangeTable.getGroupTableId() == null
					|| exchangeTable.getGroupTableId().trim().equals("null")
					|| exchangeTable.getGroupTableId().trim().equals("")) {
				String groupTableId = UUID32.newGuid();
				exchangeTable.setGroupTableId(groupTableId);
				sysEDIService.saveExcTable(exchangeTable);
				returnObject.setInfo(groupTableId);
				returnObject.setMsg("Success");
			} else {
				if (exchangeTable.getSuperTableId() == null
						|| exchangeTable.getSuperTableId().equals("")
						|| exchangeTable.getSuperTableId().equals("null")) {
					exchangeTable.setSuperTableId("");
				}
				sysEDIService.updateExcTable(exchangeTable);
				returnObject.setMsg("upSuccess");
			}
		} catch (Exception e) {
			e.printStackTrace();
			returnObject.setMsg(e.getMessage());
		}
		return returnObject;
	}

	@RequestMapping("/delExchangeTable")
	@ResponseBody
	public ReturnObject delExchangeTable(String groupTableId,
			HttpServletRequest request) {
		ReturnObject returnObject = new ReturnObject();
		try {
			sysEDIService.delExcTable(groupTableId);
			returnObject.setMsg("Success");
		} catch (Exception e) {
			e.printStackTrace();
			returnObject.setMsg(e.getMessage());
		}
		return returnObject;
	}

	/**********************************************************************
	 * 维护列枚举 *
	 ******************************/
	/**
	 * 跳转到枚举页面
	 * 
	 * @param groupId
	 * @param groupTableId
	 * @param request
	 * @return
	 */
	@RequestMapping("/maintainCln")
	public String maintainClnPage(String groupId, String groupTableId,
			HttpServletRequest request) {
		request.setAttribute("groupId", groupId);
		request.setAttribute("groupTableId", groupTableId);
		return "homepages/exchange/maintainColn";
	}

	/***
	 * 查找维护交换列枚举
	 * 
	 * @param groupId
	 * @param groupTableId
	 * @param request
	 * @return
	 */
	@RequestMapping("/queryExcClnByIds")
	@ResponseBody
	public QueryJson queryExcClnByIds(String groupId, String groupTableId,
			HttpServletRequest request) {
		QueryJson queryJson = new QueryJson();
		try {
			List<ExchangeColumn> exchangeColumns = new ArrayList<ExchangeColumn>();
			exchangeColumns = sysEDIService.queryExcClnByIds(groupId,
					groupTableId);
			queryJson.setRows(exchangeColumns);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return queryJson;
	}

	/***
	 * 加载所有列FromDS
	 * 
	 * @param groupId
	 * @param groupTableId
	 * @param request
	 * @return
	 */
	@RequestMapping("/queryExcClnFromDS")
	@ResponseBody
	public QueryJson queryExcClnFromDS(String groupId, String groupTableId,
			HttpServletRequest request) {
		QueryJson queryJson = new QueryJson();
		try {
			List<ExchangeColumn> exchangeColumns = new ArrayList<ExchangeColumn>();
			ExchangeTable exchangeTable = new ExchangeTable();
			exchangeTable = sysEDIService.loadExcTableById(groupTableId);
			if (exchangeTable.getDataSelectDsid() == null
					|| exchangeTable.getDataSelectDsid().trim().equals("")) {
				queryJson.setMsg("info");
				queryJson.setExtendinfo("请先设置数据源ID");
				return queryJson;
			}
			exchangeColumns = sysEDIService.queryExcClnFromDS(exchangeTable
					.getDataSelectDsid());
			if (exchangeColumns == null || !(exchangeColumns.size() > 0)) {
				queryJson.setMsg("info");
				queryJson.setExtendinfo("找不到枚举列，请先检查DS的字段信息");
				return queryJson;
			}
			queryJson.setMsg("success");
			queryJson.setRows(exchangeColumns);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return queryJson;
	}

	/***
	 * 加载所有列FromSQL
	 * 
	 * @param groupId
	 * @param groupTableId
	 * @param request
	 * @return
	 */
	@RequestMapping("/queryExcClnFromSQL")
	@ResponseBody
	public QueryJson queryExcClnFromSQL(String groupId, String groupTableId,
			HttpServletRequest request) {
		QueryJson queryJson = new QueryJson();
		try {
			ExchangeTable exchangeTable = new ExchangeTable();
			exchangeTable = sysEDIService.loadExcTableById(groupTableId);
			if (exchangeTable.getDataSelectDsid() == null
					|| exchangeTable.getDataSelectDsid().trim().equals("")) {
				queryJson.setMsg("info");
				queryJson.setExtendinfo("请先设置数据源ID");
				return queryJson;
			}
			DataStore dataStore = new DataStore();
			dataStore = sysEDIService.loadDataStoreById(exchangeTable
					.getDataSelectDsid());
			List<String> sqlColumnsList = sysEDIService.getSqlColumns(dataStore
					.getSqlText());
			List<ExchangeColumn> exchangeColumns = new ArrayList<ExchangeColumn>();
			for (int i = 0; i < sqlColumnsList.size(); i++) {
				ExchangeColumn exchangeColumn = new ExchangeColumn();
				exchangeColumn.setColId(UUID32.newGuid());
				exchangeColumn.setColCname(sqlColumnsList.get(i).toLowerCase());
				exchangeColumn.setColName(sqlColumnsList.get(i).toLowerCase());
				exchangeColumn.setSortNum((i + 1) * 10);
				exchangeColumns.add(exchangeColumn);
			}
			queryJson.setRows(exchangeColumns);
			queryJson.setMsg("success");
		} catch (Exception e) {
			e.printStackTrace();
			queryJson.setMsg(e.getMessage());
		}
		return queryJson;
	}

	@RequestMapping("/saveClns")
	@ResponseBody
	public ReturnObject flushSaveExcClns(
			@RequestBody List<ExchangeColumn> excColumns,
			HttpServletRequest request) {
		ReturnObject returnObject = new ReturnObject();
		try {
			if (excColumns != null && excColumns.size() > 0) {
				sysEDIService
						.delExcClnsByIds(
								" delete from sys_exchange_column where group_id = ? and group_table_id = ? ",
								excColumns.get(0).getGroupId(), excColumns.get(
										0).getGroupTableId());
			}
			sysEDIService.saveExcClns(excColumns);
			returnObject.setMsg("success");
		} catch (Exception e) {
			e.printStackTrace();
			returnObject.setMsg(e.getMessage());
		}
		return returnObject;
	}

	/**********************************************************************
	 * 映射关系定义 组 *
	 *********************************************************************/
	@RequestMapping("/queryMappingGroup")
	@ResponseBody
	public QueryJson queryMappingGroup(DataGridReceiveParams dataGParams,
			String config, HttpServletRequest request) {
		QueryJson queryJson = new QueryJson();
		try {
			QueryJson tmpQJ = sysEDIService.queryMappingGroup(dataGParams
					.getRows(), dataGParams.getPage(), config);
			queryJson.setRows(tmpQJ.getRows());
			queryJson.setTotal(tmpQJ.getTotal());
			queryJson.setUrl("../edi/queryMappingGroup");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return queryJson;
	}

	/***
	 * 加载MappingGroup对照信息
	 * 
	 * @param request
	 * @return
	 */
	@RequestMapping("/loadMappingGroupDz")
	@ResponseBody
	public List<Object> loadMappingGroupDz(HttpServletRequest request) {
		List<Object> list = new ArrayList<Object>();
		try {
			// 加载源分组、目标分组、启动模式
			List excGroup = new ArrayList<Object>();
			excGroup = sysEDIService
					.queryDzSource(DzConstant.MAPPING_GROUP_EXCHANGE_GROUP);
			List runMod = new ArrayList<Object>();
			runMod = sysEDIService
					.queryDzSource(DzConstant.MAPPING_GROUP_RUN_MOD);
			list.add(excGroup);
			list.add(runMod);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/***
	 * 保存/更新交换组
	 * 
	 * @param exchangeTable
	 * @param request
	 * @return
	 */
	@RequestMapping("/addMapGroup")
	@ResponseBody
	public ReturnObject addMapGroup(MappingGroup mappingGroup,
			HttpServletRequest request) {
		ReturnObject returnObject = new ReturnObject();
		if (mappingGroup.getIsValid() != null
				&& mappingGroup.getIsValid().equals("on")) {
			mappingGroup.setIsValid("T");
		} else {
			mappingGroup.setIsValid("");
		}
		try {
			if (mappingGroup.getGroupMappingId() == null
					|| mappingGroup.getGroupMappingId().trim().equals("null")
					|| mappingGroup.getGroupMappingId().trim().equals("")) {
				String groupMappingId = UUID32.newGuid();
				mappingGroup.setGroupMappingId(groupMappingId);
				sysEDIService.saveMapGroup(mappingGroup);
				returnObject.setInfo(groupMappingId);
				returnObject.setMsg("success");
			} else {
				sysEDIService.updateMapGroup(mappingGroup);
				returnObject.setMsg("upsuccess");
			}
		} catch (Exception e) {
			e.printStackTrace();
			returnObject.setMsg(e.getMessage());
		}
		return returnObject;
	}

	@RequestMapping("/queryMappingTable")
	@ResponseBody
	public QueryJson queryMappingTable(DataGridReceiveParams dataGParams,
			String mappingGroupId, HttpServletRequest request) {
		QueryJson queryJson = new QueryJson();
		try {
			QueryJson tmpQJ = sysEDIService.queryMappingTable(dataGParams
					.getRows(), dataGParams.getPage(), mappingGroupId);
			queryJson.setRows(tmpQJ.getRows());
			queryJson.setTotal(tmpQJ.getTotal());
			queryJson.setUrl("../edi/queryMappingTable");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return queryJson;
	}

	/**************************************************************
	 * 交换项配置
	 *************************************************************/
	/***
	 * 加载MappingTable对照信息
	 * 
	 * @param request
	 * @return
	 */
	@RequestMapping("/loadMappingTableDz")
	@ResponseBody
	public List<Object> loadMappingTableDz(HttpServletRequest request) {
		List<Object> list = new ArrayList<Object>();
		try {
			// 加载根交换项 、 加载所有交换项
			List excRootGroup = new ArrayList<Object>();
			excRootGroup = sysEDIService
					.queryDzSource(DzConstant.MAPPING_GROUP_EXCHANGE_ROOT_TABLES);
			List excGroupC = new ArrayList<Object>();
			excGroupC = sysEDIService
					.queryDzSource(DzConstant.MAPPING_GROUP_EXCHANGE_TABLES);
			list.add(excRootGroup);
			list.add(excGroupC);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/***
	 * 保存/更新交换项
	 * 
	 * @param exchangeTable
	 * @param request
	 * @return
	 */
	@RequestMapping("/addMapTable")
	@ResponseBody
	public ReturnObject addMapTable(MappingTable mappingTable,
			HttpServletRequest request) {
		ReturnObject returnObject = new ReturnObject();
		try {
			if (mappingTable.getIsDividePage() != null
					&& mappingTable.getIsDividePage().equals("on")) {
				mappingTable.setIsDividePage("T");
			} else {
				mappingTable.setIsDividePage("");
			}

			if (mappingTable.getTableMappingId() == null
					|| mappingTable.getTableMappingId().trim().equals("null")
					|| mappingTable.getTableMappingId().trim().equals("")) {
				String tableMappingId = UUID32.newGuid();
				mappingTable.setTableMappingId(tableMappingId);
				sysEDIService.saveMapTable(mappingTable);
				returnObject.setInfo(tableMappingId);
				returnObject.setMsg("success");
			} else {
				sysEDIService.updateMapTable(mappingTable);
				returnObject.setMsg("upsuccess");
			}
		} catch (Exception e) {
			e.printStackTrace();
			returnObject.setMsg(e.getMessage());
		}
		return returnObject;
	}
	/**************************************************************
	 * 字段映射配置
	 *************************************************************/

}
